package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DecimalFormat;
import java.util.List;
import java.util.TreeMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.transaction.UserTransaction;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.BaseDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;

/**
 * Clase DAO Padre, sobre la cual los demas DAOS extenderan sus funciones.
 * 
 * @author acespedes Consult-Soft S.A.
 * @version 4.0 28/11/2011
 * @since 1.4
 */
public class BaseDAOImpl implements BaseDAO {
	protected Connection conn = null;
	protected boolean connClosed;
	protected final DecimalFormat formatoNumerico = new DecimalFormat(
			"###,###,###,###,###.##");

	// ------------------------------------------------------------------------
	// Constructores
	// ------------------------------------------------------------------------

	/**
	 * Crea un BaseDAO sin inicializar.
	 */
	public BaseDAOImpl(Connection con) {
		// conn = ConexionFactory.getInstance().getConnection();
		conn = con;
		connClosed = false;
	}

	// ------------------------------------------------------------------------
	// Metodos Publicos
	// ------------------------------------------------------------------------

	public void close() {
		if (!isConnClosed()) {
			connClosed = true;
			try {
				if (conn != null) {
					/*
					 * Logger.getLogger(BaseDAOImpl.class.getName()).log(
					 * Level.INFO, "CloseBaseDAO.");
					 */
					close(conn);
				}
			} finally {
				conn = null;
			}
		}
	}

	public boolean isConnClosed() {
		return connClosed;
	}

	/**
	 * Metodo sobrecargado que cierra un preparedStatement e ignora la
	 * excepcion.
	 * 
	 * @param con
	 */
	public void close(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error al cerrarConexion:", e);
		}
	}

	/**
	 * Metodo sobrecargado que cierra un preparedStatement e ignora la
	 * excepcion.
	 * 
	 * @param rs
	 */
	public void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error al cerrarResultSet:", e);
		}
	}

	/**
	 * Metodo sobrecargado que cierra un preparedStatement e ignora la
	 * excepcion.
	 * 
	 * @param stmt
	 */
	public void close(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error al cerrarStatement:", e);
		}
	}

	/**
	 * Cierra silenciosamente la Connection, Statement y ResultSet. Cualquier
	 * error es impreso en el stderr.
	 * 
	 * @param connection
	 *            The Connection to be closed quietly.
	 * @param statement
	 *            The Statement to be closed quietly.
	 * @param resultSet
	 *            The ResultSet to be closed quietly.
	 */
	public void close(Connection connection, Statement statement,
			ResultSet resultSet) {
		close(resultSet);
		close(statement);
		close(connection);
	}

	/**
	 * Metodo que cierra el RestultSet y el Statement, sin lanzar excepcion.
	 * 
	 * @param resultSet
	 * @param statement
	 */
	public void close(ResultSet resultSet, Statement statement) {
		close(resultSet);
		close(statement);
	}

	/**
	 * Creates a Prepared Statement Query using variable arguments for a custom
	 * search, if the query has no arguments or values the query remains the
	 * same and no search params are added, this allows to make a custom search
	 * without having to add a lot of if-else statements
	 * 
	 * @param baseQuery
	 *            a database query that contains no search parameters
	 * @param queryParams
	 *            search parameters which are pairs that contain criteria and
	 *            values&#8203;&#8203;, which are added to the database query to
	 *            generate a ready to run PreparedStatement
	 * @param connection
	 *            connection to the database open and ready for querys
	 * @return returns a PreparedStatement with variable search parameters to
	 *         get data ready to run
	 */
	// public PreparedStatement setQueryParams(String baseQuery, TreeMap<String,
	// Object> queryParams,
	public PreparedStatement setQueryParams(String baseQuery,
			TreeMap queryParams, Connection connection) throws SQLException {
		StringBuffer query = new StringBuffer(baseQuery);
		PreparedStatement preparedStatement = null;
		Object[] keysObject = queryParams.keySet().toArray();
		/*
		 * we check the parameters size and if the hashmap is not empty we begin
		 * to build a query using the keys for storing the values
		 */
		if (queryParams.size() > 0) {
			boolean first = true;
			for (int i = 0; i < keysObject.length; i++) {
				if (first) {
					/*
					 * if this is the first value we add the clause "where" and
					 * set first to false so we can add the "and" clauses
					 */
					query.append(" where ");
					first = false;
				} else {
					query.append(" and ");
				}
				query.append((String) keysObject[i] + " = ? ");
			}
		}
		/* we create a prepared statement ready for accepting parameters */
		preparedStatement = connection.prepareStatement(query.toString());
		/*
		 * here we iterate through the values in the set and put the
		 * corresponding values for each one of them in the prepared statement
		 * and add them as a correct Object , the corresponding values equals
		 * the current keyset order since the treemap uses the same order for
		 * the keys
		 */
		for (int i = 0; i < keysObject.length; i++) {
			String key = (String) keysObject[i];
			Object value = queryParams.get(key);
			if (value instanceof Integer) {
				preparedStatement.setInt(i + 1,
						(Integer.parseInt(value.toString())));
			} else if (value instanceof Float) {
				preparedStatement.setFloat(i + 1,
						(Float.parseFloat(value.toString())));
			} else if (value instanceof Double) {
				preparedStatement.setDouble(i + 1,
						(Double.parseDouble(value.toString())));
			} else if (value instanceof String) {
				preparedStatement.setString(i + 1, (String) value);
			} else if (value instanceof Date) {
				long time = ((Date) value).getTime();
				preparedStatement.setDate(i + 1, new java.sql.Date(time));
			}
		}
		/* finally we return the prepared statement with all values set */
		return preparedStatement;
	}

	/**
	 * TODO: mejorar!! metodo para asignar los parametros que corresponden al
	 * PreparedStatement. La lista debe estar en el orden que corresponda a la
	 * consulta del PreparedStatement
	 * 
	 * @param pstmt
	 * @param parameters
	 * @return
	 * @throws SQLException
	 */
	public PreparedStatement setQueryParameters(PreparedStatement pstmt,
			List<? extends Object> parameters) throws SQLException {
		if (pstmt == null) {
			throw new SQLException();
		}
		if (parameters == null) {
			throw new IllegalArgumentException("Parameters list can't be null.");
		}
		try {
			for (int i = 0; i < parameters.size(); i++) {
				Object value = parameters.get(i);
				if (value instanceof Integer) {
					pstmt.setInt(i + 1, ((Integer) value).intValue());
				} else if (value instanceof Float) {
					pstmt.setFloat(i + 1, ((Float) value).floatValue());
				} else if (value instanceof Double) {
					pstmt.setDouble(i + 1, ((Double) value).doubleValue());
				} else if (value instanceof Long) {
					pstmt.setLong(i + 1, ((Long) value).longValue());
				} else if (value instanceof Short) {
					pstmt.setShort(i + 1, ((Short) value).shortValue());
				} else if (value instanceof Byte) {
					pstmt.setByte(i + 1, ((Byte) value).byteValue());
				} else if (value instanceof byte[]) {
					pstmt.setBytes(i + 1, (byte[]) value);
				} else if (value instanceof String) {
					pstmt.setString(i + 1, (String) value);
				} else if (value instanceof Date) {
					pstmt.setDate(i + 1,
							new java.sql.Date(((Date) value).getTime()));
				} else if (value instanceof Timestamp) {
					pstmt.setTimestamp(i + 1,
							new Timestamp(((Timestamp) value).getTime()));
				} else if (value instanceof Time) {
					pstmt.setTime(i + 1, (Time) value);
				} else if (value instanceof Character) {
					pstmt.setString(i + 1, ((Character) value).toString());
				} else if (value instanceof Boolean) {
					pstmt.setBoolean(i + 1, ((Boolean) value).booleanValue());
				} else if (value instanceof Blob) {
					pstmt.setBlob(i + 1, (Blob) value);
				} else if (value instanceof Clob) {
					pstmt.setClob(i + 1, (Clob) value);
				} else if (value instanceof Array) {
					pstmt.setArray(i + 1, (Array) value);
				} else if (value == null) {
					// TODO: o OracleTypes?
					pstmt.setNull(i + 1, Types.NULL);
				} else {
					System.out.println("FUCK? ");
					pstmt.setObject(i + 1, value);
				}
			}
		} catch (SQLException e) {
			throw e;
		}
		/* finally we return the prepared statement with all values set */
		return pstmt;
	}

}